import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.engine import create
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

material_warehouse_router = APIRouter(prefix="/material/tsWarehouse", tags=["储物仓管理"],
                                      dependencies=[Depends(oauth2_scheme)])


class StorageWarehouseSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fequipmentbarcode: Optional[str] = Field("", description="设备条码（支持前缀匹配）")
    fstorageflag: Optional[str] = Field("", description="存储标志编码")
    fstate: Optional[str] = Field("", description="状态编码")
    fstockno: Optional[str] = Field("", description="仓库编号")
    fpointNo: Optional[str] = Field("", description="点位编号")
    fmacno: Optional[str] = Field("", description="MAC地址（支持前缀匹配）")


@material_warehouse_router.get("/list", summary="储物仓资料查询接口")
def list_material_warehouse(search: StorageWarehouseSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.i_ts_warehouse where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fequipmentbarcode != "":
        sql = sql + " and fequipmentbarcode like concat('%', :fequipmentbarcode ,'%')"
        param["fequipmentbarcode"] = search.fequipmentbarcode
    if search.fmacno != "":
        sql = sql + " and fmacno like concat('%', :fmacno ,'%')"
        param["fmacno"] = search.fmacno
    if search.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fstorageflag != "":
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = search.fstorageflag
    if search.fstate != "":
        sql = sql + " and fstate = :fstate "
        param["fstate"] = search.fstate
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fequipmentbarcode,feuipmentid,fequipmenttag,fstorageflag,fsite_no,"
    sql = sql + " fimage,fip,fport,fon,foff,"
    sql = sql + " frfidip,frfidport,frfidid,fairip,fairport,"
    sql = sql + " flamp1,flamp2,flamp3,flamp4,fnoorder,"
    sql = sql + " fyesorder,fstate,fupddate,foperator,fmacno,"
    sql = sql + " fpoint_no,fpoint_size,fnum,fweight,flamp5,"
    sql = sql + " flamp6,fiptype,fcloseflag,funstate,frfidtype"
    sql = sql + " from lims.i_ts_warehouse where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fequipmentbarcode != "":
        sql = sql + " and fequipmentbarcode like concat('%', :fequipmentbarcode ,'%')"
        param["fequipmentbarcode"] = search.fequipmentbarcode
    if search.fmacno != "":
        sql = sql + " and fmacno like concat('%', :fmacno ,'%')"
        param["fmacno"] = search.fmacno
    if search.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fstorageflag != "":
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = search.fstorageflag
    if search.fstate != "":
        sql = sql + " and fstate = :fstate "
        param["fstate"] = search.fstate
    if search.order == "desc":
        sql = sql + " order by  fpoint_no desc "
    else:
        sql = sql + " order by  fpoint_no "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fequipmentbarcode'] = data[key][0]
        d1['feuipmentid'] = data[key][1]
        d1['fequipmenttag'] = data[key][2]
        d1['fstorageflag'] = data[key][3]
        d1['fsiteNo'] = data[key][4]
        d1['fimage'] = data[key][5]
        d1['fip'] = data[key][6]
        d1['fport'] = data[key][7]
        d1['fon'] = data[key][8]
        d1['foff'] = data[key][9]
        d1['frfidip'] = data[key][10]
        d1['frfidport'] = data[key][11]
        d1['frfidid'] = data[key][12]
        d1['fairip'] = data[key][13]
        d1['fairport'] = data[key][14]
        d1['flamp1'] = data[key][15]
        d1['flamp2'] = data[key][16]
        d1['flamp3'] = data[key][17]
        d1['flamp4'] = data[key][18]
        d1['fnoorder'] = data[key][19]
        d1['fyesorder'] = data[key][20]
        d1['fstate'] = data[key][21]
        d1['fupddate'] = data[key][22]
        d1['foperator'] = data[key][23]
        d1['fmacno'] = data[key][24]
        d1['fpointNo'] = data[key][25]
        d1['fpointSize'] = data[key][26]
        d1['fnum'] = data[key][27]
        d1['fweight'] = data[key][28]
        d1['flamp5'] = data[key][29]
        d1['flamp6'] = data[key][30]
        d1['fiptype'] = data[key][31]
        d1['fcloseflag'] = data[key][32]
        d1['funstate'] = data[key][33]
        d1['frfidtype'] = data[key][34]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class StorageWarehouseCreate(BaseModel):
    """储物仓新增请求参数"""
    fequipmentbarcode: str = Field(..., description="储物仓条码（唯一标识）")
    fsiteNo: str = Field(..., description="站点编号")
    fequipmenttag: str = Field(..., description="设备标签")
    feuipmentid: str = Field(..., description="设备ID")
    fstorageflag: str = Field("", description="存储标志编码")
    fimage: str = Field("", description="图片路径")
    fip: str = Field("", description="IP地址")
    fport: str = Field("", description="端口号")
    fon: str = Field("", description="开启状态")
    foff: str = Field("", description="关闭状态")
    frfidip: str = Field("", description="RFID IP地址")
    frfidport: str = Field("", description="RFID 端口")
    frfidid: str = Field("", description="RFID 编号")
    fairip: str = Field("", description="网络IP地址")
    fairport: str = Field("", description="网络端口")
    flamp1: str = Field("", description="指示灯1状态")
    flamp2: str = Field("", description="指示灯2状态")
    flamp3: str = Field("", description="指示灯3状态")
    flamp4: str = Field("", description="指示灯4状态")
    fnoorder: str = Field("", description="无序标志")
    fyesorder: str = Field("", description="有序标志")
    fstate: str = Field("0", description="设备状态编码")
    fmacno: str = Field("", description="MAC地址")
    fpointNo: str = Field("", description="点位编号")
    fpointSize: str = Field("", description="点位尺寸")
    fnum: str = Field("0", description="数量")
    fweight: str = Field("0", description="重量")
    flamp5: str = Field("", description="指示灯5状态")
    flamp6: str = Field("", description="指示灯6状态")
    fiptype: str = Field("1", description="IP类型编码")
    fcloseflag: str = Field("", description="关闭标志")
    funstate: str = Field("", description="使用状态")
    frfidtype: str = Field("0", description="RFID类型编码")


@material_warehouse_router.post("/create", summary="储物仓新增接口")
def create_material_warehouse(create: StorageWarehouseCreate, db: Session = Depends(get_db),
                              token: str = Depends(oauth2_scheme)):
    try:
        fequipmentbarcode = create.fequipmentbarcode
        fsiteNo = create.fsiteNo
        userid = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fsite_No from lims.i_ts_warehouse where fequipmentbarcode= :fequipmentbarcode  and fsite_No= :fsiteNo "
            data = db.execute(text(sql), {
                "fequipmentbarcode": fequipmentbarcode,
                "fsiteNo": fsiteNo,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.success(message="编号已存在")
            else:
                sql = "insert into lims.i_ts_warehouse(fequipmentbarcode,feuipmentid,fequipmenttag,fstorageflag,fsite_no,"
                sql = sql + " fimage,fip,fport,fon,foff,"
                sql = sql + " frfidip,frfidport,frfidid,fairip,fairport,"
                sql = sql + " flamp1,flamp2,flamp3,flamp4,fnoorder,"
                sql = sql + " fyesorder,fstate,fupddate,foperator,fmacno,"
                sql = sql + " fpoint_no,fpoint_size,fnum,fweight,flamp5,"
                sql = sql + " flamp6,fiptype,fcloseflag,funstate,frfidtype)"
                sql = sql + " values( :fequipmentbarcode , :feuipmentid , :fequipmenttag , :fstorageflag , :fsiteNo "
                sql = sql + ", :fimage , :fip , :fport , :fon , :foff "
                sql = sql + ", :frfidip , :frfidport , :frfidid , :fairip , :fairport "
                sql = sql + ", :flamp1 , :flamp2 , :flamp3 , :flamp4 , :fnoorder "
                sql = sql + ", :fyesorder , :fstate ,now(), :userid , :fmacno "
                sql = sql + ", :fpointNo , :fpointSize , :fnum , :fweight , :flamp5 "
                sql = sql + ", :flamp6 , :fiptype , :fcloseflag , :funstate , :frfidtype );"
                db.execute(text(sql), {
                    "fequipmentbarcode": fequipmentbarcode,
                    "feuipmentid": create.feuipmentid,
                    "fequipmenttag": create.fequipmenttag,
                    "fstorageflag": create.fstorageflag,
                    "fsiteNo": create.fsiteNo,
                    "fimage": create.fimage,
                    "fip": create.fip,
                    "fport": create.fport,
                    "fon": create.fon,
                    "foff": create.foff,
                    "frfidip": create.frfidip,
                    "frfidport": create.frfidport,
                    "frfidid": create.frfidid,
                    "fairip": create.fairip,
                    "fairport": create.fairport,
                    "flamp1": create.flamp1,
                    "flamp2": create.flamp2,
                    "flamp3": create.flamp3,
                    "flamp4": create.flamp4,
                    "fnoorder": create.fnoorder,
                    "fyesorder": create.fyesorder,
                    "fstate": create.fstate,
                    "userid": userid,
                    "fmacno": create.fmacno,
                    "fpointNo": create.fpointNo,
                    "fpointSize": create.fpointSize,
                    "fnum": create.fnum,
                    "fweight": create.fweight,
                    "flamp5": create.flamp5,
                    "flamp6": create.flamp6,
                    "fiptype": create.fiptype,
                    "fcloseflag": create.fcloseflag,
                    "funstate": create.funstate,
                    "frfidtype": create.frfidtype,
                })
                # 返回
                d1 = {}
                d1['fequipmentbarcode'] = fequipmentbarcode
                d1['feuipmentid'] = create.feuipmentid
                d1['fequipmenttag'] = create.fequipmenttag
                d1['fstorageflag'] = create.fstorageflag
                d1['fsiteNo'] = fsiteNo
                d1['fimage'] = create.fimage
                d1['fip'] = create.fip
                d1['fport'] = create.fport
                d1['fon'] = create.fon
                d1['foff'] = create.foff
                d1['frfidip'] = create.frfidip
                d1['frfidport'] = create.frfidport
                d1['frfidid'] = create.frfidid
                d1['fairip'] = create.fairip
                d1['fairport'] = create.fairport
                d1['flamp1'] = create.flamp1
                d1['flamp2'] = create.flamp2
                d1['flamp3'] = create.flamp3
                d1['flamp4'] = create.flamp4
                d1['fnoorder'] = create.fnoorder
                d1['fyesorder'] = create.fyesorder
                d1['fstate'] = create.fstate
                d1['fupddate'] = None
                d1['foperator'] = userid
                d1['fmacno'] = create.fmacno
                d1['fpointNo'] = create.fpointNo
                d1['fpointSize'] = create.fpointSize
                d1['fnum'] = create.fnum
                d1['fweight'] = create.fweight
                d1['flamp5'] = create.flamp5
                d1['flamp6'] = create.flamp6
                d1['fiptype'] = create.fiptype
                d1['fcloseflag'] = create.fcloseflag
                d1['funstate'] = create.funstate
                d1['frfidtype'] = create.frfidtype
                d1['fconnectparam'] = None
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@material_warehouse_router.post("/update", summary="储物仓修改接口")
def update_material_warehouse(update: StorageWarehouseCreate, db: Session = Depends(get_db),
                              token: str = Depends(oauth2_scheme)):
    try:
        userid = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update lims.i_ts_warehouse"
            sql = sql + " set fstorageflag= :fstorageflag ,fimage= :fimage ,fip= :fip "
            sql = sql + ",fport= :fport ,fon= :fon ,foff= :foff "
            sql = sql + ",frfidip= :frfidip ,frfidport= :frfidport ,frfidid= :frfidid "
            sql = sql + ",fairip= :fairip ,fairport= :fairport ,flamp1= :flamp1 "
            sql = sql + ",flamp2= :flamp2 ,flamp3= :flamp3 ,flamp4= :flamp4 "
            sql = sql + ",fnoorder= :fnoorder ,fyesorder= :fyesorder ,fstate= :fstate "
            sql = sql + ",fupddate=now(),foperator= :userid ,fmacno= :fmacno "
            sql = sql + ",fpoint_no= :fpointNo ,fpoint_size= :fpointSize ,fnum= :fnum "
            sql = sql + ",fweight= :fweight ,flamp5= :flamp5 ,flamp6= :flamp6 "
            sql = sql + ",fiptype= :fiptype ,fcloseflag= :fcloseflag ,funstate= :funstate "
            sql = sql + ",fequipmenttag= :fequipmenttag ,frfidtype= :frfidtype "
            sql = sql + " where fequipmentbarcode= :fequipmentbarcode  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fstorageflag": update.fstorageflag,
                "fimage": update.fimage,
                "fip": update.fip,
                "fport": update.fport,
                "fon": update.fon,
                "foff": update.foff,
                "frfidip": update.frfidip,
                "frfidport": update.frfidport,
                "frfidid": update.frfidid,
                "fairip": update.fairip,
                "fairport": update.fairport,
                "flamp1": update.flamp1,
                "flamp2": update.flamp2,
                "flamp3": update.flamp3,
                "flamp4": update.flamp4,
                "fnoorder": update.fnoorder,
                "fyesorder": update.fyesorder,
                "fstate": update.fstate,
                "userid": userid,
                "fmacno": update.fmacno,
                "fpointNo": update.fpointNo,
                "fpointSize": update.fpointSize,
                "fnum": update.fnum,
                "fweight": update.fweight,
                "flamp5": update.flamp5,
                "flamp6": update.flamp6,
                "fiptype": update.fiptype,
                "fcloseflag": update.fcloseflag,
                "funstate": update.funstate,
                "fequipmenttag": update.fequipmenttag,
                "frfidtype": update.frfidtype,
                "fequipmentbarcode": update.fequipmentbarcode,
                "fsiteNo": update.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@material_warehouse_router.post("/delete/{fequipmentbarcode}", summary="储物仓删除接口")
def delete_material_warehpuse(fequipmentbarcode: str = Path(description="储物仓条码"), db: Session = Depends(get_db)):
    try:
        param = {"fequipmentbarcode": fequipmentbarcode}
        with db.begin():
            sql = "select fequipmentbarcode from i_ts_controlbox where fequipmentbarcode= :fequipmentbarcode "
            data = db.execute(text(sql), param).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from lims.i_ts_warehouse where fequipmentbarcode= :fequipmentbarcode "
                db.execute(text(sql), param)
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
